/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.bld.model.query.consultaweb;

import br.com.bld.util.facade.AbstractDao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author Pedro
 */
class ConsultaWebQDao extends AbstractDao {

    public ConsultaWebQDao(Connection conn) {
        super(conn);
    }

    public Collection<ConsultaWebPb> consultar(String palavraChave, String cidade, String uf, Integer numrPagina, Integer qtdePorPagina) throws SQLException {

        StringBuilder sql = new StringBuilder();

        sql.append("SELECT\n");
        sql.append("    bldtb01_cliente.nome_fantasia,\n");
        sql.append("    bldtb02_endereco.logradouro,\n");
        sql.append("    bldtb02_endereco.numero,\n");
        sql.append("    bldtb07_telefone.numr_fone\n");
        sql.append("FROM\n");
        sql.append("  bld.bldtb01_cliente,\n");
        sql.append("  bld.bldtb02_endereco,\n");
        sql.append("  bld.bldtb03_municipio,\n");
        sql.append("  bld.bldtb07_telefone\n");
        sql.append("WHERE\n");
        sql.append("  bldtb01_cliente.id = bldtb02_endereco.bldtb01_id AND\n");
        sql.append("  bldtb01_cliente.id = bldtb07_telefone.bldtb01_id AND\n");
        sql.append("  bldtb02_endereco.bldtb03_codg_ibge = bldtb03_municipio.codg_ibge AND\n");
        sql.append("  bldtb03_municipio.uf ILIKE ? AND\n");
        sql.append("  bldtb03_municipio.nome ILIKE ? AND\n");
        sql.append("  bld.remove_acento(bldtb01_cliente.nome_fantasia) ILIKE bld.remove_acento(?)\n");
        sql.append("ORDER BY\n");
        sql.append("  bldtb01_cliente.nome_fantasia\n");
        sql.append("LIMIT ").append(qtdePorPagina).append(" OFFSET ").append(qtdePorPagina * numrPagina);

        PreparedStatement ps = null;
        ResultSet rs = null;

        Collection<ConsultaWebPb> resposta = new ArrayList<>();

        try {

            ps = getPreparedStatement(sql.toString());

            int i = 0;
            ps.setString(++i, uf);
            ps.setString(++i, cidade);

            palavraChave = "%" + palavraChave + "%";

            ps.setString(++i, palavraChave);
            
            rs = getResultSet(ps);

            while (rs.next()) {
                
                i = 0;
                
                ConsultaWebPb pb = new ConsultaWebPb();

                pb.setNome(rs.getString(++i));
                pb.setLogradouro(rs.getString(++i));
                pb.setNumero(rs.getString(++i));
                pb.setFone(rs.getString(++i));

                resposta.add(pb);
            }

            return resposta;

        } finally {
            close(ps, rs);
        }
    }
    
      public Collection<ConsultaWebPb> consultar(String palavraChave, Integer numrPagina, Integer qtdePorPagina) throws SQLException {

        StringBuilder sql = new StringBuilder();

        sql.append("SELECT\n");
        sql.append("    bldtb01_cliente.nome_fantasia,\n");
        sql.append("    bldtb02_endereco.logradouro,\n");
        sql.append("    bldtb02_endereco.numero,\n");
        sql.append("    array_to_string(array_agg(bldtb07_telefone.numr_fone), ',', '*') AS FONES,\n");
        sql.append("    bldtb03_municipio.nome\n");
        sql.append("FROM\n");
        sql.append("  bld.bldtb01_cliente,\n");
        sql.append("  bld.bldtb02_endereco,\n");
        sql.append("  bld.bldtb03_municipio,\n");
        sql.append("  bld.bldtb07_telefone\n");
        sql.append("WHERE\n");
        sql.append("  bldtb01_cliente.id = bldtb02_endereco.bldtb01_id AND\n");
        sql.append("  bldtb01_cliente.id = bldtb07_telefone.bldtb01_id AND\n");
        sql.append("  bldtb02_endereco.bldtb03_codg_ibge = bldtb03_municipio.codg_ibge AND\n");
        sql.append("  bld.remove_acento(bldtb01_cliente.nome_fantasia) ILIKE bld.remove_acento(?)\n");
        sql.append("  GROUP BY\n");
        sql.append("  bldtb01_cliente.nome_fantasia,\n");
        sql.append("  bldtb02_endereco.logradouro,\n");
        sql.append("  bldtb02_endereco.numero,\n");
        sql.append("  bldtb03_municipio.nome\n");
        sql.append("ORDER BY\n");
        sql.append("  bldtb01_cliente.nome_fantasia\n");
        sql.append("LIMIT ").append(qtdePorPagina).append(" OFFSET ").append(qtdePorPagina * numrPagina);

        PreparedStatement ps = null;
        ResultSet rs = null;

        Collection<ConsultaWebPb> resposta = new ArrayList<>();

        try {

            ps = getPreparedStatement(sql.toString());

            int i = 0;

            palavraChave = "%" + palavraChave + "%";

            ps.setString(++i, palavraChave);
            
            rs = getResultSet(ps);

            while (rs.next()) {
                
                i = 0;
                
                ConsultaWebPb pb = new ConsultaWebPb();

                pb.setNome(rs.getString(++i));
                pb.setLogradouro(rs.getString(++i));
                pb.setNumero(rs.getString(++i));
                pb.setFone(rs.getString(++i));
                pb.setCidade(rs.getString(++i));

                resposta.add(pb);
            }

            return resposta;

        } finally {
            close(ps, rs);
        }
    }
}
